"""
   It provides various database table manipulation functions, including creating table by giving the specific columns and datatypes, or altering table
"""


import MySQLdb
import sys
from DatabaseConnector import connection

_DEBUG_ = True

def CreateTable(db_name, tbl_name, attributes, cursor):
    print "Start to create table " + db_name + '.' + tbl_name + "...\n"
    sql = 'create table ' + db_name + '.' + tbl_name + ' ('
    try:
        for i in range(0, len(attributes)):
            column = list(str(attributes[i]).split())[0]
            datatype = list(str(attributes[i]).split())[1]
    #         sql = 'create table stoplist1 (stopword varchar(40))'
            sql = sql + column + ' ' + datatype 
            if len(attributes) != 1 and i < (len(attributes)-1):
                sql = sql + ' , '
        sql = sql + ')'
#        print sql
        cursor.execute(sql)
      
    except MySQLdb.OperationalError, message:
        errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
        print errorMessage
        

def IfTableExist(db_name, table_name):
    try:
        cursor = connection.cursor()
        sql = "select * from information_schema.tables where table_name = '" + table_name + "' and table_schema = '" + db_name + "'"
        cursor.execute(sql)
        if cursor.rowcount == 0:
            return False
        else:
            return True
        
    except MySQLdb.OperationalError, message:
        errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
        print errorMessage

def ImportData(db_name, tbl_name, if_data, cursor):
    if if_data == "0":
        return
    try:
        f = open("LexSCT/%s" % tbl_name,"r")
        counter = 0
        for i,line in enumerate(f):
            if _DEBUG_:
                if i % 50 == 0:
                    sys.stderr.write(".")
            if line[0] == '#':
                continue
            counter =  counter + 1
            temp_line = line.split(";")
            if len(temp_line) == 1:
                sql = 'insert into ' + db_name + '.' + tbl_name + ' values ("' + temp_line[0].strip() + '")'
            else:
                sql = 'insert into ' + db_name + '.' + tbl_name + ' values ("'
                for i in range(0, len(temp_line)-1):
                    sql = sql + temp_line[i].strip() + '","'
                sql = sql + temp_line[i+1].strip() + '")'
            print sql
            cursor.execute(sql)
        print "\t%d rows have been inserted.\n" % (counter)
        f.close()
        connection.commit()
    except MySQLdb.OperationalError, message:
        errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
        print errorMessage  
         
def InitializeDatabase():
    cursor = connection.cursor()
    print "Starting to initialize the database...\n"
    try:    
        for line in file("LexSCT/tblschema", "r"):
            if str(line)[0] == '#':
                continue
            else:
                tblname = list(str(line).strip().split(";"))[0]
                dbname = list(str(line).strip().split(";"))[1]
                if_data = list(str(line).strip().split(";"))[2]
                
                tblname = tblname.strip()
                dbname = dbname.strip()
                if_data = if_data.strip()
                ls = list(str(line).strip().split(";"))
                ls.__delitem__(0)
                ls.__delitem__(0)
                ls.__delitem__(0)
                
                
                if IfTableExist(dbname, tblname):
                    cursor.execute("drop table " + dbname + "." + tblname)
                    connection.commit()
                    
                CreateTable(dbname, tblname, ls, cursor)
                ImportData(dbname, tblname, if_data, cursor)
                print "Table " + dbname + "." + tblname + " was successfully created.\n"
        
        print "Database initialization has been accomplished.\n"
                
    except MySQLdb.OperationalError, message:
        errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
        print errorMessage
        
#        if extbl == tblname:
#             f = open("LexSCT/%s" % tblname,"r");
#             try:
#                 for i,line in enumerate(f):
#                     if _DEBUG_:
#                        if i % 50 == 0:
#                           sys.stderr.write(".")
#                     sql = 'insert into ' + tblname + ' values ("' + line.strip() + '")'
#                     cursor.execute(sql)
#                     connection.commit()
#                 print "\t%d rows have been inserted" % (i+1)
#             except MySQLdb.OperationalError, message:
#                    errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
#                    print errorMessage
#             f.close()
#           else:
#                print "Cannot open the source file. Please check the name of the table."

def UninstallDatabase():
    cursor = connection.cursor()
    try:    
        for line in file("LexSCT/tblschema", "r"):
            if str(line)[0] == '#':
                continue
            else:
                tblname = list(str(line).strip().split(";"))[0]
                dbname = list(str(line).strip().split(";"))[1]
                
                tblname = tblname.strip()
                dbname = dbname.strip()
                if IfTableExist(dbname, tblname):
                    cursor.execute("drop table " + dbname + "." + tblname)
                    connection.commit()
                    print "Table " + dbname + "." + tblname + " has been dropped.\n"
                    
        print "Database uninstallation has been accomplished.\n"
    except MySQLdb.OperationalError, message:
        errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] )
        print errorMessage    

def StrToList():
    string = "1,2,3"
    print list(string.split(",")), string


if __name__ == "__main__":
    UninstallDatabase()
    InitializeDatabase()
#    columns = ['stopword']
#    datatypes = ['varchar(40)']
#    CreateTable("stoplist1", columns, datatypes)
# #       CreateTable()
# #     ents = [('&', '&amp;'),('"', '&quot;'),('<', '&lt;'),('>', '&gt;'),('\'', '\\\''),('"', '\\\"')]
# #     _LoadStopWord()
#      list1 = load_stopwords_old()
#      list2 = load_stopwords_new()
#      if len(list1) != len(list2):
#         print "Different length!!"
#      else:
#           for word in list1:
#               if word not in list2:
#                  print "Error in matching!!"
#           print "Perfectly matched ^_^"


